/*
 Navicat Premium Data Transfer

 Source Server         : geer-RDS-Test
 Source Server Type    : MySQL
 Source Server Version : 80028
 Source Host           : rm-bp1m0g9lus067n9yxco.mysql.rds.aliyuncs.com:3306
 Source Schema         : ede-signup-test

 Target Server Type    : MySQL
 Target Server Version : 80028
 File Encoding         : 65001

 Date: 05/04/2024 21:54:14
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_api_sql
-- ----------------------------
DROP TABLE IF EXISTS `sys_api_sql`;
CREATE TABLE `sys_api_sql` (
  `model` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '模块',
  `code` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '编码',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '接口名称',
  `sqlcn` varchar(4000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '代码内容',
  `id` int NOT NULL AUTO_INCREMENT,
  `childs` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '子类',
  `leftinfo` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '左关联',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of sys_api_sql
-- ----------------------------
BEGIN;
INSERT INTO `sys_api_sql` VALUES ('match', 'match', '赛事列表', 'select \nt.id\n,t.match_name \n,t.match_level\n,t.season\n,case when t.begin_signup <now() and t.end_signup >now() then 1 else 0 end signflag\n,DATE_FORMAT(t.begin_signup,\'%Y-%m-%d %H:%i:%s\') begin_signup\n,DATE_FORMAT(t.end_signup,\'%Y-%m-%d %H:%i:%s\') end_signup\n,DATE_FORMAT(t.begin_date,\'%Y-%m-%d %H:%i:%s\') begindate \n,concat(DATE_FORMAT(t.begin_date,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_date,\'%m月%d日\')) matchdate \n,concat(DATE_FORMAT(t.begin_signup,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_signup,\'%m月%d日\')) signdate \n from sp_match t  where 1=1 and t.`status`=1 if:id[ and t.id =#{p.id} ]\nand (\n    \n\nt.match_level  = 1 or #{p.user.province} = t.province\n)\nand (\n\nt.match_level  <=2  or t.is_open = 1 or #{p.user.city} = t.city or t.city = \'-1\'\n)\n', 4, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchRecord', '报名记录', 'select \nt.match_name \n,t.match_level\n,sj.dict_label seasonname\n,concat(DATE_FORMAT(t.begin_date,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_date,\'%m月%d日\')) begindate\n,concat(DATE_FORMAT(t.begin_signup,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_signup,\'%m月%d日\')) signdate  , t.id ,t.id detail\n from sp_match t \nleft join sys_dict_data sj on sj.id = t.season\n\n where t.id in (\n \n select  mi.match_id from sp_match_item_team t2 \ninner join sp_match_item  mi on mi.id = t2.match_item_id\ninner join sp_match m on m.id = mi.match_id\ninner join sp_match_item_team_user muser on muser.match_item_team_id = t2.id\nand (#{p.user.roleId} !=4 or  muser.user_id = #{p.user.id} )\nif:userId[ \n\nand muser.user_id = #{p.userId}\n]\ninner  join sp_user uu on uu.id = muser.user_id\nwhere t2.team_id = #{p.user.teamId}\n if:matchStatus[ \nand t2.status = #{p.matchStatus}\n]\n if:searchStr[and ( INSTR(uu.real_name,#{p.searchStr})>0 or INSTR(t.match_name,#{p.searchStr})>0 )]\n ) \n\nif:endDate[and t.end_date < #{ p.endDate}]\nif:beginDate[ and t.begin_date > #{ p.beginDate}]\n if:season[ and t.season=#{p.season}]\n if:matchLevel[and t.match_level =#{p.matchLevel} ]\n if:province[and t.province =#{p.province}]\n if:city[and t.city =#{p.city}]\norder by t.end_date desc', 5, 'matchRecordItem:detail:detail:userId:searchStr:itemType', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchRecordItem', '报名记录明细', 'select\nt.id\n,t.rise_file\n,t.rise_times\n,t.teacher_upd_times\n,DATE_FORMAT(t.create_date,\'%Y-%m-%d %H:%i:%s\') create_date\n,mi.id match_item_id\n,mi.draw_open\n,mi.score_open\n ,mi.match_id\n,mi.notice_title\n,mi.notice_content\n,m.match_name\n,tm.team_name company_name\n,tm.leader\n,bi.item_name\n,DATE_FORMAT(mi.notice_time,\'%Y-%m-%d %H:%i:%s\') notice_time\n,CONCAT(DATE_FORMAT(m.begin_date,\'%Y-%m-%d\'),\'~\',DATE_FORMAT(m.end_date,\'%Y-%m-%d\')) match_date\n, (select count(1) from sp_notice_item_log log where log.item_id = mi.id and log.user_id = t.id) readCount\n,t.team_id\n,mclass.dict_label classname\n,mtype.dict_label typename ,\n t.team_name ,bi.item_name ,g.dict_label group_name , t.sort , bi.is_draw\n,(\nselect GROUP_CONCAT(csu.real_name SEPARATOR \'、\') from  sp_match_item_team cmi \ninner join sp_match_item_team_user cmu on cmu.match_item_team_id = cmi.id\ninner join sp_user csu on csu.id = cmu.user_id \n\nwhere cmu.type=1 and cmi.id=t.id \n)\nreal_name ,\n(\nselect GROUP_CONCAT(tk.entry_pic SEPARATOR \'、\') from  \nsp_match_item_team cmi \ninner join sp_match_item_team_user cmu on cmu.match_item_team_id = cmi.id\ninner join sp_user csu on csu.id = cmu.user_id \ninner join sp_match_item mi on cmi.match_item_id = mi.id  \ninner join sp_ticket tk on cmu.user_id = tk.user_id and tk.match_id =  mi.match_id\nwhere cmu.type=1 and cmi.id=t.id \n) ticket_url,\n(\nselect GROUP_CONCAT(csu.id SEPARATOR \'、\') from  sp_match_item_team cmi \ninner join sp_match_item_team_user cmu on cmu.match_item_team_id = cmi.id\ninner join sp_user csu on csu.id = cmu.user_id \nwhere cmu.type=1 and cmi.id=t.id \n\n)user_ids ,\n\nt.`status`\nif:myinfo[\n\n,(\nselect CONCAT(GROUP_CONCAT(csu.real_name SEPARATOR \'、\'), \',\',  GROUP_CONCAT(csu.id SEPARATOR \'、\')) from  sp_match_item_team cmi \ninner join sp_match_item_team_user cmu on cmu.match_item_team_id = cmi.id\ninner join sp_user csu on csu.id = cmu.user_id \nwhere cmu.type=2 and cmi.id=t.id\n) teachers\n\n]\n \n\nfrom sp_match_item_team t\n INNER JOIN sp_match_item mi on mi.id = t.match_item_id\ninner join sp_match m on m.id = mi.match_id\nleft join sp_base_match_item bi on bi.id = mi.base_match_item_id\nleft join sp_team tm on tm.id = t.team_id\nleft join sys_dict_data mclass on mclass.id = bi.item_classify\nleft join sys_dict_data mtype on mtype.id = bi.item_type\nleft join sys_dict_data g on t.group_id = g.id\nif:userId[\ninner join sp_match_item_team_user smuser on  smuser.match_item_team_id =t.id\nand smuser.user_Id =#{p.userId}  and smuser.type=1\n]\n\nwhere 1=1 if:detail[ and  mi.match_id = #{p.detail}]\nand t.team_id =#{p.user.teamId}\nif:id[ and  t.id = #{p.id}]\nif:itemType[ and bi.item_type =#{p.itemType}]\nif:searchStr[\nand exists(\nselect 1 from  sp_match_item_team cmi \ninner join sp_match_item_team_user cmu on cmu.match_item_team_id = cmi.id\ninner join sp_user csu on csu.id = cmu.user_id \nwhere cmu.type=1 and cmi.id=t.id and instr(csu.real_name ,#{p.searchStr})>0\n)\n]\n', 6, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('user', 'userList', '用户列表', 'select \nt.id \n,t.real_name\n,t.company\n,DATE_FORMAT(t.create_date,\'%Y-%m-%d %H:%i\') create_date \n,t2.id teamUserId\n,t.id_card\nfrom sp_user t \nleft join sp_team_user t2 on t.id = t2.user_id\nwhere 1=1   \n	and t2.team_id =#{p.user.teamId}\n	and ( #{p.user.roleId}  !=4 or  t.id = #{p.user.id} )\n	if:type[ and t2.type=#{p.type}  ] \n	if:realName[ and t.real_name like concat(\'%\', #{p.realName}, \'%\')  ] \n	if:season[and (select count(1) from sp_match_item_team_user r \n			inner join sp_match_item_team rm on rm.id = r.match_item_team_id\n			inner join sp_match_item sumi on sumi.id = rm.match_item_id\n			inner join sp_match usm on usm.id = sumi.match_id\n			where \n				r.user_id =t.id and usm.season =#{p.season}\n				and rm.status not in (3,7,8)\n		)<2]\n	if:noItemId[and not exists ( \n		select si.* from sp_match_item_team_user r \n		inner  join sp_match_item_team  si on si.id = r.match_item_team_id \n		where  si.match_item_id = #{p.noItemId} and r.user_id =  t.id  and r.status!=0 \n			and si.status not in (3,7,8)\n	)]', 7, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchInfo', '赛事详情', 'select t.match_name \n,DATE_FORMAT(t.begin_date,\'%Y-%m-%d %H:%i:%s\') begindate \n,concat(DATE_FORMAT(t.begin_date,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_date,\'%m月%d日\'))  matchdate\n,concat(DATE_FORMAT(t.begin_signup,\'%m月%d日\') ,\'~\',DATE_FORMAT(t.end_signup,\'%m月%d日\')) signdate\n,t.id detail \n from sp_match t \nwhere t.id =#{p.id}', 8, 'matchItem:detail:matchid', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'matchItem', '赛事明细', 'select t.id\n,t.match_id\n,t.base_match_item_id\n,bi.item_classify\n,bi.item_type\n\n,mclass.dict_label classname\n,mtype.dict_label typename \n,bi.item_name \n,t.teacher_num\n,t.teacher_num_max\n,t.student_num\n,t.student_num_max \nfrom sp_match_item t \nleft join sp_base_match_item bi on bi.id = t.base_match_item_id\nleft join sys_dict_data mclass on mclass.id = bi.item_classify\nleft join sys_dict_data mtype on mtype.id = bi.item_type\nwhere 1=1  if:matchid[ and t.match_id = #{p.matchid} ]\n if:id[  and t.id = #{p.id} ]\n order by mclass.create_date asc', 9, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('dict', 'dictList', '字典', 'select t.id code ,t.dict_label name from sys_dict_data t \nwhere t.dict_type_id =#{p.pid}', 10, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('user', 'team', '代表队', 'select su.real_name,t.team_name ,t.id from sp_team t \nleft join sp_team_user stu on stu.team_id = t.id and stu.type =1\nleft join sp_user su on su.id = stu.user_id\nwhere  1=1\nif:id[ and t.id =#{p.id}]', 12, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('sys', 'dictList', '字典', 'select t.id  code ,t.dict_label name ,t.dict_value  value  from  sys_dict_data t \nwhere 1=1 \nand t.dict_type_id=#{p.pid}\norder by t.sort;', 13, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('sys', 'areaDict', '地区接口', 'select t.code,t.name  from  t_area t where t.pcode=#{p.pid} and t.name !=\'直辖区\'', 14, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('match', 'signRecord', '后台报名记录', 'SELECT\n	t.id,\n	t.team_id,\n	tm.team_name,\n	bi.item_name,\n	t.team_name,\n	group_concat(u.real_name  separator \'、\') real_name\nFROM\n	sp_match_item_team t,\n	sp_match_item_team_user tu,\n	sp_user u,\n  sp_team tm,\n	sp_base_match_item bi\nWHERE\n	tu.match_item_team_id = t.id\n	and tu.user_id = u.id\n	and t.team_id = tm.id\n	and t.match_item_id = bi.id\n	and tu.type =1 \n	group by \n	t.id,\n	t.team_id,\n	tm.team_name,\n	bi.item_name,\n	t.team_name', 15, ' ', NULL);
INSERT INTO `sys_api_sql` VALUES ('user', 'userInfo', '用户详情', 'select  t.id ,t.real_name ,t.sex ,t.company ,t.header ,t.email,t.id_card,t.id_type\nif:matchId[\n,st.ticket \n]\nfrom sp_user t \nif:matchId[\nleft join sp_ticket st on st.user_id = t.id and st.match_id=#{p.matchId}\n]\nwhere t.id =#{p.id}', 16, '', NULL);
INSERT INTO `sys_api_sql` VALUES ('notice', 'notice', '通知', 'select t.id,t.title,t.content, DATE_FORMAT(t.create_date,\'%Y-%m-%d %H:%i\')  create_date,\nn.user_id,\nn.notice_id\n from sp_notice t \nleft join (select DISTINCT user_id,notice_id from sp_notice_log where 1=1 and user_id= #{p.user.id}) n \non n.notice_id = t.id \nwhere 1=1 \nand t.business_id in (\n	select DISTINCT mi.match_id from sp_match_item_team mit \n		 left join sp_match_item mi ON mit.match_item_id = mi.id\n		 where mit.team_id \n		 in (\n				SELECT\n					mitu.team_id\n				FROM\n					sp_match_item_team_user mitu\n				where mitu.user_id = #{p.user.id}\n			) \n)\nif:type[and t.type = #{p.type}]', 17, ' ', NULL);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
